﻿package org.electric.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.electric.bean.CompanyBean;
import org.electric.bean.industryBean;
import org.electric.bean.productBean;
import org.electric.util.JDBCUtil;


/**
 * 企业dao
 * @author Administrator
 *
 */
public class CompanyDao {
	//注册
//	public int addCompanyBean(CompanyBean combean) throws SQLException{
//		Connection conn=JDBCUtil.getConection();
//		
//		int count=0;
//		StringBuffer sb=new StringBuffer();
//		sb.append("INSERT INTO `electrical`.`company_info`( `company_user`, "
//				+ "`company_password`, "
//				+ "`company_name`, "
//				+ "`register_money`, "
//				+ "`employee_num`, "
//				+ "`address`, "
//				+ "`license_id`, "
//				+ "`status`) "
//				+ "VALUES ( ?, ?, ?, ?, ?, ?, ?, 0)");
//		PreparedStatement  pre=conn.prepareStatement(sb.toString());
//		pre.setString(1, combean.getCompany_user());
//		pre.setString(2, combean.getCompany_password());
//		pre.setDouble(3, combean.getRegister_money());
//		pre.setInt(4, combean.getEmployee_num());
//		pre.setString(5, combean.getAddress());
//		pre.setString(6, combean.getLicense_id());
//		pre.setInt(7, combean.getStatus());
//		count=pre.executeUpdate();	
//		JDBCUtil.close(conn, pre);
//		return count;
//	}
//	//登录
//	public CompanyBean login(CompanyBean combean) throws Exception{
//		Connection conn=JDBCUtil.getConection();
//		CompanyBean  cb=new CompanyBean();
//		StringBuffer sb=new StringBuffer();
//		sb.append("SELECT * from company_info WHERE company_user=? AND company_password=? limit 1");
//		PreparedStatement  ps=conn.prepareStatement(sb.toString());
//		ps.setString(1, combean.getCompany_user());
//		ps.setString(2, combean.getCompany_password());
//		ResultSet rs=ps.executeQuery();
//		while(rs.next()){	
//			cb.setLicense_id(rs.getString("license_id"));	
//		}
//		JDBCUtil.close(conn, ps, rs);
//		return cb;
//	}
//	//通过营业执照 号 查询出该公司的所有 产品；
//	public List<productBean> selectproduct(String license) throws SQLException{
//		List<productBean> list= new ArrayList<productBean>();
//		Connection conn=JDBCUtil.getConection();
//		StringBuffer sb=new StringBuffer();
//		//spare表示状态；spare=1表示存在，spare=0表示删除
//		sb.append("select * from  product_info WHERE  license_id=? and spare2=1");
//		PreparedStatement  ps=conn.prepareStatement(sb.toString());
//		ps.setString(1, license);
//		ResultSet rs=ps.executeQuery();
//		while(rs.next()){
//			//创建一个productBean
//			productBean pb=new productBean();
//			pb.setProduct_id(rs.getString("product_id"));
//			pb.setProduct_name(rs.getString("product_name"));
//			pb.setProduct_desc(rs.getString("product_desc"));
//			pb.setLicense_id(rs.getString("license_id"));
//			pb.setImage(rs.getString("image"));
//			list.add(pb);
//		}
//		JDBCUtil.close(conn, ps, rs);
//		return list;
//	}
//	
	
	//nub表示前端传回来的页码是多少
	public List<productBean> productDisplay(int nub) throws SQLException{
		List<productBean> list= new ArrayList<productBean>();
		Connection conn=JDBCUtil.getConection();
		StringBuffer sb=new StringBuffer();
		sb.append("SELECT * FROM product_info WHERE status=1 limit ?,9");
		PreparedStatement  ps=conn.prepareStatement(sb.toString());
		ps.setInt(1, (nub-1)*9);
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			productBean product=new productBean();
			product.setProduct_id(rs.getString("product_id"));
			product.setProduct_name(rs.getString("product_name"));
			product.setProduct_desc(rs.getString("product_desc"));
			product.setLicense_id(rs.getString("license_id"));
			product.setImage(rs.getString("image"));
			list.add(product);	
		}
		JDBCUtil.close(conn, ps, rs);
		return list;
	}
	//获取产品总数
	public int getproductcount() throws SQLException{
		int count=0;
		Connection conn=JDBCUtil.getConection();
		StringBuffer sb=new StringBuffer();
		sb.append("SELECT count(*)  FROM product_info where status=1");
		PreparedStatement  ps=conn.prepareStatement(sb.toString());
		ResultSet rs=ps.executeQuery();
		if(rs.next()){
		count =rs.getInt(1);
		}
		return count;
	}
	public CompanyBean selectCompany(CompanyBean company) throws SQLException{
		Connection con=JDBCUtil.getConection();
		StringBuffer sb=new StringBuffer();
		sb.append("SELECT * from company_info where company_user=? and company_password=? and register_status=1 and status=1");
		PreparedStatement ps=con.prepareStatement(sb.toString());
		ps.setString(1, company.getCompany_user());
		ps.setString(2, company.getCompany_password());
		ResultSet rs=ps.executeQuery();
		CompanyBean cb=new CompanyBean();
		while(rs.next()){
			cb.setCompany_user(rs.getString("company_user"));
			cb.setCompany_password(rs.getString("company_password"));
			cb.setAddress(rs.getString("address"));
			cb.setCompany_name(rs.getString("company_name"));
			cb.setEmployee_num(rs.getInt("employee_num"));
			cb.setLicense_id(rs.getString("license_id"));
			cb.setTel(rs.getString("tel"));
			cb.setRegister_money(rs.getDouble("register_money"));
			cb.setRegister_time(rs.getString("register_time"));
		}
		return cb;
	}
	public int selectCompanyExist(CompanyBean company) throws SQLException{
		Connection con=JDBCUtil.getConection();
		StringBuffer sb=new StringBuffer();
		int row=0;
		sb.append("SELECT count(1) from company_info where company_user=? and company_password=? and register_status=1 and status=1");
		PreparedStatement ps=con.prepareStatement(sb.toString());
		ps.setString(1, company.getCompany_user());
		ps.setString(2, company.getCompany_password());
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			row=rs.getInt(1);
		}
		return row;
		
	}

	public List<CompanyBean> selectAllCompany() throws SQLException{
		List<CompanyBean> list=new ArrayList<CompanyBean>();
		StringBuffer sql=new StringBuffer();
		sql.append("select company_user,company_password,address,company_name,employee_num,license_id,tel,register_money,DATE_FORMAT(register_time,'%Y-%m-%d') AS register_time from company_info where 1=1");
		Connection con=JDBCUtil.getConection();
		PreparedStatement ps=con.prepareStatement(sql.toString());
		ResultSet rs=ps.executeQuery();
		while(rs.next()){
			CompanyBean cb=new CompanyBean();
			cb.setCompany_user(rs.getString("company_user"));
			cb.setCompany_password(rs.getString("company_password"));
			cb.setAddress(rs.getString("address"));
			cb.setCompany_name(rs.getString("company_name"));
			cb.setEmployee_num(rs.getInt("employee_num"));
			cb.setLicense_id(rs.getString("license_id"));
			cb.setTel(rs.getString("tel"));
			cb.setRegister_money(rs.getDouble("register_money"));
			cb.setRegister_time(rs.getString("register_time"));
			list.add(cb);
		}
		return list;
	}
	//获取新闻的总条数
			public int getindustry() throws SQLException{
				int count=0;
				Connection conn=JDBCUtil.getConection();
				StringBuffer sb=new StringBuffer();
				sb.append("SELECT count(*) FROM industry_dynamic_info where status=1");
				PreparedStatement  ps=conn.prepareStatement(sb.toString());
				ResultSet rs=ps.executeQuery();
				if(rs.next()){
				count =rs.getInt(1);
				}
				JDBCUtil.close(conn, ps, rs);
				return count;
			}
			//numb 表示新闻显示页面返回来的页码数
			public List<industryBean> industryDisplay(int numb) throws SQLException{
				List<industryBean> list= new ArrayList<industryBean>();
				Connection conn=JDBCUtil.getConection();
				StringBuffer sb=new StringBuffer();
				sb.append("SELECT dynamic_title,dynamic_content,DATE_FORMAT(dynamic_date,'%Y-%m-%d') as dynamic_date FROM industry_dynamic_info WHERE status=1 ORDER BY dynamic_date desc LIMIT ?,5 ");
				PreparedStatement  ps=conn.prepareStatement(sb.toString());
				ps.setInt(1, (numb-1)*5);
				ResultSet rs=ps.executeQuery();
				while(rs.next()){
					industryBean ib=new industryBean();
					ib.setDynamic_title(rs.getString("dynamic_title"));
					ib.setDynamic_content(rs.getString("dynamic_content"));
					
					System.out.println(rs.getString("dynamic_date"));
					ib.setDynamic_date(rs.getString("dynamic_date"));
					list.add(ib);
				}
				JDBCUtil.close(conn, ps, rs);
				return list;
			} 
}
